package com.cdu.xinmiao.data;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

    // 数据库版本和名称
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "XinMiao.db";

    // 表名
    private static final String TABLE_STUDENTS = "Students";
    private static final String TABLE_TASKS = "Tasks";
    private static final String TABLE_STUDENT_TASK_COMPLETION = "StudentTaskCompletion";
    private static final String TABLE_CAMPUS_NEWS = "CampusNews";
    private static final String TABLE_STUDENT_ORIGIN = "StudentOrigin";
    private static final String TABLE_AD_PUSH_RECORD = "AdPushRecord";
    private static final String TABLE_AD_CLICK_RECORD = "AdClickRecord";
    private static final String TABLE_LEADERBOARD = "Leaderboard";
    private static final String TABLE_FEEDBACK = "Feedback";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建 Students 表
        String CREATE_STUDENTS_TABLE = "CREATE TABLE " + TABLE_STUDENTS + "("
                + "StudentID INTEGER PRIMARY KEY,"
                + "Name VARCHAR(255),"
                + "StudentNumber VARCHAR(255),"
                + "PhoneNumber VARCHAR(255),"
                + "IDNumber VARCHAR(255),"
                + "Photo BLOB,"
                + "Password VARCHAR(255),"
                + "Location VARCHAR(255),"
                + "EnrollmentDate DATE,"
                + "OtherInfo TEXT" + ")";
        db.execSQL(CREATE_STUDENTS_TABLE);

        // 创建 Tasks 表
        String CREATE_TASKS_TABLE = "CREATE TABLE " + TABLE_TASKS + "("
                + "TaskID INTEGER PRIMARY KEY,"
                + "TaskType VARCHAR(255),"
                + "Description TEXT,"
                + "Location VARCHAR(255),"
                + "TaskTime DATETIME,"
                + "Deadline DATETIME,"
                + "TaskStatus VARCHAR(255),"
                + "CurrentStudentsCompletingTask INT" + ")";
        db.execSQL(CREATE_TASKS_TABLE);

        // 创建 StudentTaskCompletion 表
        String CREATE_STUDENT_TASK_COMPLETION_TABLE = "CREATE TABLE " + TABLE_STUDENT_TASK_COMPLETION + "("
                + "RecordID INTEGER PRIMARY KEY,"
                + "StudentID INTEGER,"
                + "TaskID INTEGER,"
                + "CheckInPhoto BLOB,"
                + "CompletionTime DATETIME,"
                + "FOREIGN KEY (StudentID) REFERENCES Students(StudentID),"
                + "FOREIGN KEY (TaskID) REFERENCES Tasks(TaskID)" + ")";
        db.execSQL(CREATE_STUDENT_TASK_COMPLETION_TABLE);

        // 创建 CampusNews 表
        String CREATE_CAMPUS_NEWS_TABLE = "CREATE TABLE " + TABLE_CAMPUS_NEWS + "("
                + "NewsID INTEGER PRIMARY KEY,"
                + "Title VARCHAR(255),"
                + "Content TEXT,"
                + "PublishTime DATETIME" + ")";
        db.execSQL(CREATE_CAMPUS_NEWS_TABLE);

        // 创建 StudentOrigin 表
        String CREATE_STUDENT_ORIGIN_TABLE = "CREATE TABLE " + TABLE_STUDENT_ORIGIN + "("
                + "RecordID INTEGER PRIMARY KEY,"
                + "OriginLocation VARCHAR(255),"
                + "Population INT" + ")";
        db.execSQL(CREATE_STUDENT_ORIGIN_TABLE);

        // 创建 AdPushRecord 表
        String CREATE_AD_PUSH_RECORD_TABLE = "CREATE TABLE " + TABLE_AD_PUSH_RECORD + "("
                + "RecordID INTEGER PRIMARY KEY,"
                + "AdID INTEGER,"
                + "AdImage BLOB,"
                + "PushTime DATETIME" + ")";
        db.execSQL(CREATE_AD_PUSH_RECORD_TABLE);

        // 创建 AdClickRecord 表
        String CREATE_AD_CLICK_RECORD_TABLE = "CREATE TABLE " + TABLE_AD_CLICK_RECORD + "("
                + "RecordID INTEGER PRIMARY KEY,"
                + "UserID INTEGER,"
                + "AdID INTEGER,"
                + "ClickTime DATETIME,"
                + "FOREIGN KEY (UserID) REFERENCES Students(StudentID),"
                + "FOREIGN KEY (AdID) REFERENCES AdPushRecord(AdID)" + ")";
        db.execSQL(CREATE_AD_CLICK_RECORD_TABLE);

        // 创建 Leaderboard 表
        String CREATE_LEADERBOARD_TABLE = "CREATE TABLE " + TABLE_LEADERBOARD + "("
                + "RecordID INTEGER PRIMARY KEY,"
                + "StudentID INTEGER,"
                + "Points INT,"
                + "CompletionTime DATETIME,"
                + "FOREIGN KEY (StudentID) REFERENCES Students(StudentID)" + ")";
        db.execSQL(CREATE_LEADERBOARD_TABLE);

        // 创建 Feedback 表
        String CREATE_FEEDBACK_TABLE = "CREATE TABLE " + TABLE_FEEDBACK + "("
                + "RecordID INTEGER PRIMARY KEY,"
                + "StudentID INTEGER,"
                + "Content TEXT,"
                + "FeedbackTime DATETIME,"
                + "FOREIGN KEY (StudentID) REFERENCES Students(StudentID)" + ")";
        db.execSQL(CREATE_FEEDBACK_TABLE);

        // 插入测试数据
        insertTestData(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 这里可以处理数据库的升级
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENT_TASK_COMPLETION);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CAMPUS_NEWS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENT_ORIGIN);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_AD_PUSH_RECORD);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_AD_CLICK_RECORD);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LEADERBOARD);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_FEEDBACK);

        onCreate(db);
    }

    private void insertTestData(SQLiteDatabase db) {
        // 插入 Students 表测试数据
        ContentValues values = new ContentValues();
        values.put("StudentID", 1);
        values.put("Name", "洪衍松");
        values.put("StudentNumber", "202110414403");
        values.put("PhoneNumber", "17828471748");
        values.put("IDNumber", "987654321");
        values.put("Password", "password123");
        values.put("Location", "Location 1");
        values.put("EnrollmentDate", "2023-01-01");
        values.put("OtherInfo", "Some additional info");
        db.insert(TABLE_STUDENTS, null, values);

        values.clear();
        values.put("StudentID", 2);
        values.put("Name", "Jane Smith");
        values.put("StudentNumber", "20230002");
        values.put("PhoneNumber", "0987654321");
        values.put("IDNumber", "123456789");
        values.put("Password", "password456");
        values.put("Location", "Location 2");
        values.put("EnrollmentDate", "2023-01-02");
        values.put("OtherInfo", "Some more info");
        db.insert(TABLE_STUDENTS, null, values);

        // 插入 Tasks 表测试数据
        values.clear();
        values.put("TaskID", 1);
        values.put("TaskType", "Homework");
        values.put("Description", "Complete your math homework.");
        values.put("Location", "Classroom 101");
        values.put("TaskTime", "2023-01-10 10:00:00");
        values.put("Deadline", "2023-01-15 23:59:59");
        values.put("TaskStatus", "Pending");
        values.put("CurrentStudentsCompletingTask", 5);
        db.insert(TABLE_TASKS, null, values);

        // 插入 StudentTaskCompletion 表测试数据
        values.clear();
        values.put("RecordID", 1);
        values.put("StudentID", 1);
        values.put("TaskID", 1);
        values.put("CompletionTime", "2023-01-12 12:00:00");
        db.insert(TABLE_STUDENT_TASK_COMPLETION, null, values);

        // 插入 CampusNews 表测试数据
        values.clear();
        values.put("NewsID", 1);
        values.put("Title", "Welcome to the New Semester");
        values.put("Content", "We are excited to welcome all new and returning students.");
        values.put("PublishTime", "2023-01-01 08:00:00");
        db.insert(TABLE_CAMPUS_NEWS, null, values);

        // 插入 StudentOrigin 表测试数据
        values.clear();
        values.put("RecordID", 1);
        values.put("OriginLocation", "City A");
        values.put("Population", 1000);
        db.insert(TABLE_STUDENT_ORIGIN, null, values);

        // 插入 AdPushRecord 表测试数据
        values.clear();
        values.put("RecordID", 1);
        values.put("AdID", 1);
        values.put("PushTime", "2023-01-05 09:00:00");
        db.insert(TABLE_AD_PUSH_RECORD, null, values);

        // 插入 AdClickRecord 表测试数据
        values.clear();
        values.put("RecordID", 1);
        values.put("UserID", 1);
        values.put("AdID", 1);
        values.put("ClickTime", "2023-01-05 09:05:00");
        db.insert(TABLE_AD_CLICK_RECORD, null, values);

        // 插入 Leaderboard 表测试数据
        values.clear();
        values.put("RecordID", 1);
        values.put("StudentID", 1);
        values.put("Points", 100);
        values.put("CompletionTime", "2023-01-10 12:00:00");
        db.insert(TABLE_LEADERBOARD, null, values);

        // 插入 Feedback 表测试数据
        values.clear();
        values.put("RecordID", 1);
        values.put("StudentID", 1);
        values.put("Content", "This is a feedback.");
        values.put("FeedbackTime", "2023-01-15 14:00:00");
        db.insert(TABLE_FEEDBACK, null, values);
    }
}

